package com.dfbz.dao;

import com.dfbz.constant.Const;
import com.dfbz.dao.base.BaseDao;
import com.dfbz.model.User;
import com.dfbz.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao<User> {

    /**
     * 用户登录的sql
     *
     * @param username 用户名
     * @return User对象
     */
    public User findUserByName(String username) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        BasicRowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
        User user = null;
        try {
            user = qr.query("select * from user where username = ?", new BeanHandler<>(User.class, processor), username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 获取用户信息的sql
     *
     * @param userId 用户ID
     * @return Map对象
     */
    public Map<String, Object> findUserById(Integer userId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select u.id,u.username,u.dept_id as deptId,d.`name` as deptName " +
                    "from user u " +
                    "left join dept d on u.dept_id = d.id " +
                    "where u.id = ?";
            map = qr.query(sql, new MapHandler(), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }


    /**
     * 获取用户邮箱
     * @param email 用户邮箱
     * @return user对象
     */
    public User findUserByEmail(String email) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        BasicRowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
        User user = null;
        try {
            user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class, processor), email);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     *分页查询用户列表信息
     * @param userId 用户ID
     * @param page 当前页数
     * @return 用户信息
     */
    public List<Map<String, Object>> findList(Integer userId, int page, String search) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id as userId, ui.real_name as realName,");
            sql.append("ui.age,ui.gender,ui.describe as introduce,u.username,uf.id as ufId ");
            sql.append("from user_info ui ");
            sql.append("left join user u on ui.user_id = u.id ");
            sql.append("left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? ");
            if (null == search) {
                sql.append("order by u.id limit ?,? ");
                maps = qr.query(sql.toString(), new MapListHandler(), userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            } else {
                sql.append("where ui.real_name like ? or u.username like ? ");
                sql.append("order by u.id limit ?,? ");
                search = search + "%";
                maps = qr.query(sql.toString(), new MapListHandler(), userId, search, search, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    /**
     * 查询用户列表数量
     *
     * @return 总数
     */
    public long getTotalUser(String search) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from user_info ui left join user u on ui.user_id = u.id";
            if (null == search) {
                Map<String, Object> map = qr.query(sql, new MapHandler());
                total = Long.parseLong(map.get("total").toString());
            } else {
                sql = sql + " where ui.real_name like ? or u.username like ? ";
                search = search + "%";
                Map<String, Object> map = qr.query(sql, new MapHandler(),search,search);
                total = Long.parseLong(map.get("total").toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
}
